/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package billing;

import java.sql.*;
import java.util.Vector;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

/**
 *
 * @author apple
 */
public class My_Model {
    
    protected Connection conn;
    protected String tableName;
    protected int lastInsertId;
    
    public My_Model() throws Exception{
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:billing.sqlite");
        lastInsertId = -1;
    }
    
    public List<HashMap<String,String>> getData(String sql) throws Exception{
        PreparedStatement st = conn.prepareStatement(sql);
        ResultSet rs = st.executeQuery();
        ResultSetMetaData meta = rs.getMetaData();
        List<HashMap<String,String>> list = new LinkedList<HashMap<String, String>>();
        while(rs.next()){
            HashMap<String,String> row = new HashMap<String, String>();
            for(int i=1 ; i<=meta.getColumnCount() ; i++){
                row.put(meta.getColumnName(i), rs.getString(i));
            }
            list.add(row);
        }

        return list;
    }
    
    public int insertData(HashMap<String,String> data) throws Exception{

        String columns = "";
        String columnValue = "";
        
        // fetch all columns name
        int i = 1;
        for(String key : data.keySet()){
            columns += key;
            if(i < data.size()) columns += ",";
            i++;
        }
        
        // fetch all values
        i = 1;
        for(String value : data.values()){
            columnValue += "'" + value + "'";
            if(i < data.size()) columnValue += ",";
            i++;
        }
        String sql = "INSERT INTO " + tableName + "(" + columns + ") VALUES("+columnValue+")";
        PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        st.executeUpdate();
        ResultSet ids = st.getGeneratedKeys();
        if(ids.next()){
            lastInsertId = ids.getInt(1);
        }else {
            throw new SQLException("Creating user failed, no generated key obtained.");
        }
        
        
        return lastInsertId;
    }
    
    public int updateData(HashMap<String,String> data) throws Exception{

        Set<String> columns = data.keySet();
        String columnValue = "";
        // fetch all columns name
        Iterator iter = columns.iterator();
        int i=1;
        while(iter.hasNext()){
            String columnName = (String)iter.next();
            columnValue += columnName + "='" + data.get(columnName) + "'";
            if(i<data.size()) columnValue += ",";
            i++;
        }

        String sql = "UPDATE " + tableName + " SET "+columnValue + "WHERE id = " + data.get("id");
        PreparedStatement st;
        st = conn.prepareStatement(sql);
        lastInsertId = st.executeUpdate();
        
        return lastInsertId;
    }
    
    public Vector to_models(List<HashMap<String,String>> data) throws Exception{
        Vector<My_Model> result = new Vector<My_Model>();
        
        for(HashMap<String,String> model : data){
            result.add(this.to_model(model));
        }
        return result;
    }
    
    public My_Model to_model(HashMap<String,String> data) throws Exception{
        return new My_Model();
    }
    
    /*
     * -------------------------------------------------------------------------
        UTILITY METHODS
     * -------------------------------------------------------------------------
     */
    
    public Vector getElements() throws Exception{
        String sql = String.format("SELECT * FROM %s", tableName);
        return to_models(getData(sql));
    }

}
